package com.mycompany.inventory.persistence;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.Address;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class AddressDAO extends AbstractDao<Address>{
	
	private Connection con = this.connect;
	public static final String DELETE_SQL = "DELETE FROM oce_address WHERE addressId = ?";
	public static final String SELECT_ALL_SQL = "select * from oce_address";
	public static final String SELECT_SQL = "select * from oce_address where addressId = ?";
	public static final String SELECT_ADDRESS_VENDOR_SQL = "SELECT c.* FROM oce_vendor a, oce_owner_address b, oce_address c WHERE c.addressId = b.address_id AND a.vendor_id = b.vendor_id AND b.vendor_id = ?";
	public static final String SELECT_ADDRESS_CUSTOMER_SQL = "SELECT c.* FROM oce_customer a, oce_owner_address b, oce_address c WHERE c.addressId = b.address_id AND a.customer_id = b.vendor_id AND b.vendor_id = ?";
	public static final String INSERT_SQL = "INSERT INTO oce_address  (addressId, address_name, street, country, state_province, zip_postal_code, remarks, address_type) VALUES (?,?,?,?,?,?,?,?)";
	public static final String UPDATE_SQL = "UPDATE oce_address SET   address_name = ? , street = ?, country = ? , state_province = ?, zip_postal_code = ?, remarks = ? , address_type = ? " +
											"WHERE addressId = ? ";
	
	public Address create(Address obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, "");
			ps.setString(3, obj.getAddress());
			ps.setString(4, obj.getNational());
			ps.setString(5, obj.getCity());			
			ps.setString(6, "");	
			ps.setString(7, obj.getRemark());			
			ps.setString(8, obj.getType());
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}
	
	public Address update(Address obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = Long.parseLong(obj.getId());
			ps.setLong(8,id);
			ps.setString(1, "");
			ps.setString(2, obj.getAddress());
			ps.setString(3, obj.getNational());
			ps.setString(4, obj.getCity());			
			ps.setString(5, "");	
			ps.setString(6, obj.getRemark());			
			ps.setString(7, obj.getType());
			ConnectionDatabase.executeUpdate(ps);	
			
			ps.close();		
		}catch (Exception e) {
			// TODO: handle exception
		}
		return obj;
	}
	
	public Address find(long id){
		List<Address> addresses = new ArrayList<Address>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			addresses = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
		}
		return addresses.size()== 0 ? null : addresses.get(0);
	}
	
	public List<Address> findAll(){
		List<Address> addresses = new ArrayList<Address>();
		try{
			addresses = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			addresses = null;
		}
		return addresses;
	}
	
	public List<Address> findByVendorID(long id){
		List<Address> addresses = new ArrayList<Address>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_ADDRESS_VENDOR_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			addresses = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			addresses = null;
		}
		return addresses;
	}
	
	public List<Address> findByCustomerID(long id){
		List<Address> addresses = new ArrayList<Address>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_ADDRESS_CUSTOMER_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			addresses = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			addresses = null;
		}
		return addresses;
	}	
	
	public void delete(Address obj){
		try{
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getId()));
			ConnectionDatabase.executeUpdate(ps);	
			ps.close();		
		}catch (Exception e) {
			obj = null;
		}
	}
	
	private List<Address> convert(ResultSet rs) throws SQLException {
		List<Address> addresses = new ArrayList<Address>();
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String street = rs.getString(3);
	        String country = rs.getString(4);
	        String state = rs.getString(5);
	        String code = rs.getString(6);
	        String remark = rs.getString(7);
	        String type = rs.getString(8);
	        addresses.add(new Address(id, street, state , country , remark, type));
	    }
		rs.close();
		return addresses;
	}
}
